﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using CodeGenerator.interfaces;
using System.Data.SqlClient;


namespace CodeGenerator.Implementations
{
   public class SQLServerDatabaseProvider : IDatabaseProvider
    {
        public DatabaseEngines DatabaseEngine
        {
            get { return DatabaseEngines.SqlServer; }
        }

        private SqlConnection _connection = new System.Data.SqlClient.SqlConnection();
        private SqlCommand _command = new SqlCommand();
        private SqlDataAdapter _dataAdapter;

        private IGeneratorModel _model;
        private string _db;
        private string _user;
        private string _pwd;
        private string _server;
        private bool _trustedConnection = false;
        private string _connectionString;
        public SQLServerDatabaseProvider(IGeneratorModel Model, string server, string db, string user, string pwd, bool TrustedConnection)
        {
            _trustedConnection = TrustedConnection;
            _model = Model;
            _server = server;
            _db = db;
            _user = user;
            _pwd = pwd;
            if(_trustedConnection)
                _connectionString = String.Format("Server={0};Database={1};Trusted_connection=true", _server, _db);
            else
                _connectionString = String.Format("Server={0};Database={1};User Id={2};Password={3};", _server, _db, _user, _pwd);
                
            OpenConnection();
        }

        private void OpenConnection()
        {
            _connection.ConnectionString = _connectionString;
            _connection.Open();
            _command = _connection.CreateCommand();
            _command.CommandType = CommandType.Text;
            _dataAdapter = new SqlDataAdapter(_command);
            EnsureDb();
        }

       private void EnsureDb()
       {
           _command.CommandText = @"use " + _db;
           _command.ExecuteNonQuery();
       }

        public IList<IGeneratorClass> GetTables()
        {
            IList<IGeneratorClass> res = new List<IGeneratorClass>();
            DataTable dt = new DataTable();
            _command.CommandText = @"SELECT * FROM sys.Tables where name not like 'YAF%'";
            _dataAdapter.Fill(dt);
            return (from DataRow dr in dt.Rows
                    select new DataBaseTable(_model, dr["name"].ToString(), this)
                        into dataBaseTable
                        select new GeneratorClass(Common.Formatting.ToString_OneTwoThree(dataBaseTable.TableName), dataBaseTable)).Cast<IGeneratorClass>().ToList();
           
        }
        private DataTable Get1RowFromTable(string tableName)
        {
            DataTable dt = new DataTable();
            _command.CommandText = @"SELECT top 1 * FROM [dbo].[" + tableName + "]";
            _dataAdapter.Fill(dt);
            return dt;
        }

       private List<string> GetIdentityColumnsFromTable(string tableName)
       {
           DataTable dt = new DataTable();
           _command.CommandText = string.Format(@"SELECT         
               t.TABLE_NAME
              ,c.COLUMN_NAME
              ,c.TABLE_CATALOG
              ,c.TABLE_SCHEMA
              ,c.Data_type
FROM         
              INFORMATION_SCHEMA.COLUMNS AS c JOIN
              INFORMATION_SCHEMA.TABLES AS t
                      ON t.TABLE_NAME = c.TABLE_NAME
WHERE         
               COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
                      ,c.COLUMN_NAME,'IsIdentity') = 1 AND
               t.TABLE_TYPE = 'Base Table' AND
               t.TABLE_NAME = '{0}'", tableName);
            _dataAdapter.Fill(dt);

            return (from DataRow n in dt.Rows select n["Column_name"].ToString()).ToList();
       }
        public IList<IDatabaseColumn> GetTableColumns(string tableName)
        {
            
            //the types are get from the type that ado set. Yes, it could be better....
            DataTable dtColumns = GetTableColumnsWithDBTypeDefinition(tableName);
          
            DataTable dt = Get1RowFromTable(tableName);
            List<string> identities = GetIdentityColumnsFromTable(tableName);

            IList<IDatabaseColumn> result = new List<IDatabaseColumn>();
            foreach (DataColumn dc in dt.Columns)
            {

                if (dc.DataType == typeof (System.Object))
                {
                    MessageBox.Show(
                        "Unrecognized type found on column " + Environment.NewLine + Environment.NewLine + tableName +
                        "." + dc.ColumnName + Environment.NewLine + Environment.NewLine +
                        "The column will be mapped to type Object", "Warning", MessageBoxButtons.OK,
                        MessageBoxIcon.Warning);
                }
                var dbCol = (from DataRow dr in dtColumns.Rows
                             where dr["column_name"].ToString() == dc.ColumnName.ToString()
                             select dr).Single();

                string dataTypeOnDB = dbCol["Data_type"].ToString();
                string maxLength = "";
                string precision = "";
                string scale = "";
                if(dbCol["CHARACTER_MAXIMUM_LENGTH"]!=null)
                    maxLength = dbCol["CHARACTER_MAXIMUM_LENGTH"].ToString();

                if (dbCol["NUMERIC_PRECISION"] != null)
                    precision = dbCol["NUMERIC_PRECISION"].ToString();

                if (dbCol["NUMERIC_SCALE"] != null)
                    scale = dbCol["NUMERIC_SCALE"].ToString();

                DatabaseColumn newCol = new DatabaseColumn(dc.ColumnName, dc.DataType, identities.Contains(dc.ColumnName), dataTypeOnDB, maxLength, precision, scale);
                
                result.Add(newCol);
            }
            return result; 
        }

       private DataTable GetTableColumnsWithDBTypeDefinition(string tableName)
       {
           string qry = @"SELECT         
               t.TABLE_NAME
              ,c.COLUMN_NAME
              ,c.TABLE_CATALOG
              ,c.TABLE_SCHEMA
              ,c.Data_type
              ,c.CHARACTER_MAXIMUM_LENGTH
		      ,c.NUMERIC_PRECISION
			  ,c.NUMERIC_SCALE
FROM         
              INFORMATION_SCHEMA.COLUMNS AS c JOIN
              INFORMATION_SCHEMA.TABLES AS t
                      ON t.TABLE_NAME = c.TABLE_NAME
WHERE         
         
               t.TABLE_TYPE = 'Base Table' AND
               t.TABLE_NAME = '" + tableName + "'";
           DataTable dt = new DataTable();
           _command.CommandText = qry;
           _dataAdapter.Fill(dt);
           return dt;
       }

       public IList<IDatabaseColumn> GetTablePrimaryKeyColumns(string tableName)
        {
            List<IDatabaseColumn> result = new List<IDatabaseColumn>();
            string qry = "SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = '" + tableName + "'";
            DataTable dt = new DataTable();
            _command.CommandText = qry;
            _dataAdapter.Fill(dt);

            foreach (DataRow dataRow in dt.Rows)
            {
                result.Add((from n in GetTableColumns(tableName) where n.ColumnName.ToUpper() == dataRow["column_name"].ToString().ToUpper() select n).Single());
            }
            return result;
        }

        public IList<IForeignKey> GetTableForeignKeys(string tableName, IGeneratorModel Model)
        {
            IList<IForeignKey> fks = new List<IForeignKey>();
            string qry =
                @"
                            SELECT
                            K_Table = FK.TABLE_NAME,
                            FK_Column = CU.COLUMN_NAME,
                            PK_Table = PK.TABLE_NAME,
                            PK_Column = PT.COLUMN_NAME,
                            Constraint_Name = C.CONSTRAINT_NAME
                            FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
                            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
                            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
                            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
                            INNER JOIN (
                            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
                            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                            ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
                            and PK.TABLE_NAME='" + tableName + "' order by FK.TABLE_NAME, CU.ORDINAL_POSITION";
            DataTable dt = new DataTable();
            _command.CommandText = qry;
            _dataAdapter.Fill(dt);
       
            foreach (DataRow dataRow in dt.Rows)
            {
                DatabaseColumn localColumn = new DatabaseColumn(dataRow["PK_Column"].ToString(),GetTableColumnType(tableName,dataRow["PK_Column"].ToString()));
                DatabaseColumn remoteColumn = new DatabaseColumn(dataRow["FK_Column"].ToString(), GetTableColumnType(dataRow["K_Table"].ToString(), dataRow["FK_Column"].ToString()));
                DataBaseColumnPair dp = new DataBaseColumnPair(localColumn,remoteColumn);
                
                
                if((from n in fks where n.ForeignKeyName == dataRow["Constraint_Name"].ToString() select n).Count()==0)
                    fks.Add( new ForeignKey(_model, tableName, dataRow["Constraint_Name"].ToString(), new List<IDataBaseColumnPair>(){dp}, dataRow["K_Table"].ToString(), dataRow["Constraint_Name"].ToString() + "_" + dataRow["fk_column"].ToString()));
                else
                    (from n in fks where n.ForeignKeyName == dataRow["Constraint_Name"].ToString() select n).Single().Columns.Add(dp);
            }
            return fks;
        }
        private Type GetTableColumnType(string TableName, string ColumnName)
        {
            return (
                       from IDatabaseColumn n in GetTableColumns(TableName)
                       where n.ColumnName.ToUpper() == ColumnName.ToUpper()
                       select n.DataType
                   ).Single();
        }
        public IList<IUniqueConstraint> GetUniqueIndexes(string tableName)
        {
            List<IUniqueConstraint> result = new List<IUniqueConstraint>();
            string qry = @"
                            SELECT 
                                      ind.name idxName  ,ind.index_id     ,ic.index_column_id     ,col.name ColumnName
                            FROM sys.indexes ind 
                            INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
                            INNER JOIN sys.columns col   ON ic.object_id = col.object_id and ic.column_id = col.column_id 
                            INNER JOIN sys.tables t ON ind.object_id = t.object_id 
                            WHERE (1=1) 
                                AND ind.is_primary_key = 0 
                                AND ind.is_unique = 1 
                                --AND ind.is_unique_constraint = 0 
                                AND t.is_ms_shipped = 0 
	                            and t.name = '" + tableName + @"'
                            ORDER BY 
                                t.name, ind.name, ind.index_id, ic.index_column_id ";
            DataTable dt = new DataTable();
            _command.CommandText = qry;
            _dataAdapter.Fill(dt);
            
            foreach (DataRow dataRow in dt.Rows)
            {
                DatabaseColumn col = new DatabaseColumn(dataRow["ColumnName"].ToString(), GetTableColumnType(tableName, dataRow["ColumnName"].ToString()));
                if ((from n in result where n.ConstraintName == dataRow["IdxName"].ToString() select n).Count() > 0)
                    (from n in result where n.ConstraintName == dataRow["IdxName"].ToString() select n).Single().Columns.Add(col);
                else
                    result.Add(new UniqueConstraint(dataRow["IdxName"].ToString(),new List<IDatabaseColumn>(){col}));
            }
            return result;
           
        }
    }
}